﻿
/*
 * Rebuild UDS_SMS_Msg and UDS_SMS_Receiver tables
 */
IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[UDS_SMS_Receiver]') AND [type] in (N'U'))
    DROP TABLE [dbo].[UDS_SMS_Receiver]
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[UDS_SMS_Msg]') AND [type] in (N'U'))
    DROP TABLE [dbo].[UDS_SMS_Msg]
GO

CREATE TABLE [dbo].[UDS_SMS_Msg]
(
    [ID]        int IDENTITY(1,1) NOT NULL,
    [Sender]    nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [Content]   nvarchar(255) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [SendTime]  datetime NOT NULL   CONSTRAINT [DF_UDS_SMS_Msg_SendTime] DEFAULT(getdate()),
    [Type]      int NOT NULL,

    CONSTRAINT [PK_UDS_SMS_Msg] PRIMARY KEY NONCLUSTERED ( [ID] ASC ),
    CONSTRAINT [FK_UDS_SMS_Msg_Sender] FOREIGN KEY (Sender) REFERENCES dbo.UDS_Staff (Staff_Name)
);
CREATE CLUSTERED INDEX [IX_UDS_SMS_Msg_Sender] ON dbo.UDS_SMS_Msg (Sender asc);
GO

CREATE TABLE [dbo].[UDS_SMS_Receiver]
(
    [MsgID]     int NOT NULL,
    [Receiver]  nvarchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL, /* for phone messages, the column should be 'anonymous' */
    [MobileNo]  nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
    [Type]      int NULL,
    [IsRead]    bit NULL CONSTRAINT [DF_UDS_SMS_Receiver_IsRead] DEFAULT(0),

    CONSTRAINT [PK_UDS_SMS_Receiver] PRIMARY KEY CLUSTERED (Receiver asc, MsgID asc),
    CONSTRAINT [FK_UDS_SMS_Receiver_MsgID] FOREIGN KEY (MsgID) REFERENCES dbo.[UDS_SMS_Msg] (ID)
)
GO
